package com.nagat.ejb.entity;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.ejb.Stateless;
import javax.naming.InitialContext;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.sql.DataSource;

import oracle.jdbc.OracleTypes;

import org.hibernate.Session;
import org.jboss.ejb3.entity.HibernateSession;

import com.nagat.ejb.test.PropertiesUtil;

@Stateless
public class OneToOneDaoBean implements OneToOneDao{

	@Override
	public void callNoReturnValueProcedure() {

		Query query = em.createNativeQuery("{ call PR_ADD_PERSON() }");
		
		query.executeUpdate();
		
	}

	@Override
	public void callReturnSingleObjProcedure() {
		
		try {
			
			InitialContext ctx = new InitialContext();
			
			DataSource ds = (DataSource)ctx.lookup("java:/scott");

			Connection connection = ds.getConnection();
			
			CallableStatement cs = connection.prepareCall("{ call PR_QUERY_PERSON_NAME(?,?) }");
		
			cs.setInt(1, 2325);
			cs.registerOutParameter(2, OracleTypes.VARCHAR);
			cs.execute();
			
			String name = cs.getString(2);
			
			cs.close();
			connection.close();
			
			System.out.println(name);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	

	@Override
	public void callRetrunCursorProcedure() {
		
		try {
			
			InitialContext ctx = new InitialContext();
			
			DataSource ds = (DataSource)ctx.lookup("java:/scott");

			Connection connection = ds.getConnection();
			
			CallableStatement cs = connection.prepareCall("{ call PR_QUERY_PERSONS(?) }");
		
			cs.registerOutParameter(1, OracleTypes.CURSOR);
			cs.execute();
			
			ResultSet rs = (ResultSet) cs.getObject(1);
			
			while(rs.next()){
				System.out.println(rs.getString(4));
				System.out.println(rs.getString(3));
			}
			cs.close();
			connection.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}



	@PersistenceContext
	protected EntityManager em;
	
	@Override
	public void add(Person p) {
		// TODO Auto-generated method stub
		em.persist(p);
	}

	@Override
	public void update(Integer id, String name, String cardno) {

		
		Person p = find(id);
		
		p.setName(name);
		p.getIdcard().setCardno(cardno);
		
	}

	@Override
	public Person find(Integer id) {
		// TODO Auto-generated method stub
		Person p = em.find(Person.class, id);
		
		Query query = em.createNamedQuery("getPerson");
		
		query.setParameter(1, id);
		p = (Person)query.getSingleResult();
		
		return p;
	}

	@Override
	public List<Person> findList() {

		Query query = em.createQuery("select p from Person p inner join fetch p.idcard ");
		
		
		return query.getResultList();
	}

	@Override
	public void delete(Integer id) {

		em.remove(em.find(Person.class, id));
	}

}
